﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace Library
{
    public class clSudung
    {
        private SqlConnection sqlcon;
        private SqlCommand sqlcmd;
        public static SqlConnection GetConnection()
        {
            string strConnect = ConfigurationManager.ConnectionStrings["StrConnectString"].ToString();
            SqlConnection sqlcon = new SqlConnection(strConnect);
            return sqlcon;
        }

        public int addsudung(string maCu, Int64 ngaydoc, int socu, int somoi, string nvten)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_sudung_nhapsodh", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@maCu", SqlDbType.NVarChar,25).Value = maCu;
                    sqlcmd.Parameters.Add("@ngaydoc", SqlDbType.BigInt).Value = ngaydoc;
                    sqlcmd.Parameters.Add("@socu", SqlDbType.Int).Value = socu;
                    sqlcmd.Parameters.Add("@somoi", SqlDbType.Int).Value = somoi;
                    sqlcmd.Parameters.Add("@nvten", SqlDbType.NVarChar, 50).Value = nvten;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public DataTable sdSudung()
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_sudung_danhsach", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable dsNhapso()
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_sudung_danhsach_nhapso", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable getDataQuery(Int16 thang, Int16 nam, Int16 val, string type) // tinh theo %
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_sudung_query", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@thang", SqlDbType.SmallInt).Value = thang;
                    sqlcmd.Parameters.Add("@nam", SqlDbType.SmallInt).Value = nam;
                    sqlcmd.Parameters.Add("@val", SqlDbType.SmallInt).Value = val;
                    sqlcmd.Parameters.Add("@type", SqlDbType.NVarChar, 2).Value = type;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable getDataBatthuong(Int16 thang, Int16 nam, Int16 val, string type) // tinh theo %
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_sudung_batthuong", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@thang", SqlDbType.SmallInt).Value = thang;
                    sqlcmd.Parameters.Add("@nam", SqlDbType.SmallInt).Value = nam;
                    sqlcmd.Parameters.Add("@val", SqlDbType.SmallInt).Value = val;
                    sqlcmd.Parameters.Add("@type", SqlDbType.NVarChar, 2).Value = type;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

    }
}
